﻿using Dapper;
using MySql.Data.MySqlClient;
using RankingWorld.Models;
using Microsoft.Extensions.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace RankingWorld.Repositories
{
    public class RankingRepository : IRankingRepository
    {
        private readonly string _connectionString;

        public RankingRepository(IConfiguration configuration)
        {
            _connectionString = configuration.GetConnectionString("DefaultConnection");
        }

        public async Task<IEnumerable<Ranking>> GetAllRankingsAsync()
        {
            using (IDbConnection db = new MySqlConnection(_connectionString))
            {
                return await db.QueryAsync<Ranking>("SELECT * FROM Rankings");
            }
        }

        public async Task<Ranking> GetRankingByIdAsync(int id)
        {
            using (IDbConnection db = new MySqlConnection(_connectionString))
            {
                return await db.QueryFirstOrDefaultAsync<Ranking>("SELECT * FROM Rankings WHERE Id = @Id", new { Id = id });
            }
        }

        public async Task<int> AddRankingAsync(Ranking ranking)
        {
            using (IDbConnection db = new MySqlConnection(_connectionString))
            {
                var sql = "INSERT INTO Rankings (Name, Score) VALUES (@Name, @Score); SELECT LAST_INSERT_ID();";
                return await db.ExecuteScalarAsync<int>(sql, ranking);
            }
        }

        public async Task<int> UpdateRankingAsync(Ranking ranking)
        {
            using (IDbConnection db = new MySqlConnection(_connectionString))
            {
                var sql = "UPDATE Rankings SET Name = @Name, Score = @Score WHERE Id = @Id";
                return await db.ExecuteAsync(sql, ranking);
            }
        }

        public async Task<int> DeleteRankingAsync(int id)
        {
            using (IDbConnection db = new MySqlConnection(_connectionString))
            {
                var sql = "DELETE FROM Rankings WHERE Id = @Id";
                return await db.ExecuteAsync(sql, new { Id = id });
            }
        }
    }
}